﻿CREATE TABLE kind_of_activity (
	kind_of_activity_id INT IDENTITY CONSTRAINT pk_kind_of_activity_id 
						PRIMARY KEY (kind_of_activity_id) NOT NULL,
	kind_of_activity_description NVARCHAR(500) NULL
)
GO

CREATE TABLE skill (
	skill_id INT IDENTITY CONSTRAINT pk_skill_id PRIMARY KEY (skill_id) NOT NULL,
	skill_description NVARCHAR(500) NULL
)
GO

CREATE TABLE employer (
	employer_id INT IDENTITY CONSTRAINT pk_employer_id PRIMARY KEY (employer_id) NOT NULL,
	employer_nm NVARCHAR(200) NOT NULL,
	kind_of_activity_id INT NOT NULL,
	address_txt NVARCHAR(500) NULL,
	telephone_nbr NVARCHAR(15) NULL
)
GO

CREATE TABLE vacancy (
	vacancy_id INT IDENTITY(1,1) CONSTRAINT pk_vacancy_id PRIMARY KEY (vacancy_id) NOT NULL,
	vacancy_nm NVARCHAR(100) NOT NULL,
	employer_id INT CONSTRAINT fk_employer_id FOREIGN KEY (employer_id)
				REFERENCES employer (employer_id) NOT NULL,
	kind_of_activity_id INT CONSTRAINT fk_kind_of_activity_id FOREIGN KEY (kind_of_activity_id)
						REFERENCES kind_of_activity (kind_of_activity_id) NOT NULL,
	open_vacancy_dtm DATETIME NOT NULL,
	location_txt NVARCHAR(500) NOT NULL,
	expected_lvl_of_salary_amt INT NOT NULL
)
GO

CREATE TABLE skills_for_vacancy (
	vacancy_id INT CONSTRAINT fk_vacancy_id FOREIGN KEY (vacancy_id)
			   REFERENCES vacancy (vacancy_id) NOT NULL,
	skill_id INT CONSTRAINT fk_skill_id FOREIGN KEY (skill_id)
			 REFERENCES skill (skill_id) NOT NULL
)
GO

ALTER TABLE skills_for_vacancy ADD CONSTRAINT pk_skills_for_vacancy_id PRIMARY KEY (vacancy_id, skill_id)
